package com.openspring.framework.jdbc.sql;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class SqlWrapper {

	private String schema;
	private String tableName;
	private String alias;
	private String join;
	private Map<String, String> primaryKeyColumns = new LinkedHashMap<String, String>();
	private Map<String, String> insertColumns = new LinkedHashMap<String, String>();
	private Map<String, String> updateColumns = new LinkedHashMap<String, String>();
	private List<String> selectColumns = new ArrayList<String>();
	
	public String getSchema() {
		return schema;
	}
	public void setSchema(String schema) {
		this.schema = schema;
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public String getAlias() {
		return alias;
	}
	public void setAlias(String alias) {
		this.alias = alias;
	}
	public String getJoin() {
		return join;
	}
	public void setJoin(String join) {
		this.join = join;
	}
	public Map<String, String> getPrimaryKeyColumns() {
		return primaryKeyColumns;
	}
	public void setPrimaryKeyColumns(Map<String, String> primaryKeyColumns) {
		this.primaryKeyColumns = primaryKeyColumns;
	}
	public Map<String, String> getInsertColumns() {
		return insertColumns;
	}
	public void setInsertColumns(Map<String, String> insertColumns) {
		this.insertColumns = insertColumns;
	}
	public Map<String, String> getUpdateColumns() {
		return updateColumns;
	}
	public void setUpdateColumns(Map<String, String> updateColumns) {
		this.updateColumns = updateColumns;
	}
	public List<String> getSelectColumns() {
		return selectColumns;
	}
	public void setSelectColumns(List<String> selectColumns) {
		this.selectColumns = selectColumns;
	}
	
	
	public SqlWrapper addPrimaryKeyColumn(String columnName) {
		addPrimaryKeyColumn(columnName, "?");
		return this;
	}

	public SqlWrapper addPrimaryKeyColumn(String columnName, String valueExpression) {
		primaryKeyColumns.put(columnName, valueExpression);
		return this;
	}
	
	public SqlWrapper addInsertColumn(String columnName) {
		return addInsertColumn(columnName, "?");
	}

	public SqlWrapper addInsertColumn(String columnName, String valueExpression) {
		insertColumns.put(columnName, valueExpression);
		return this;
	}
	
	public SqlWrapper addUpdateColumn(String columnName) {
		return addUpdateColumn(columnName, "?");
	}

	public SqlWrapper addUpdateColumn(String columnName, String valueExpression) {
		updateColumns.put(columnName, valueExpression);
		return this;
	}
	
	public String toDeleteAll() {
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM ").append(schema).append('.').append(tableName);
		return sb.toString();
	}
	
	public String toDeleteById() {
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM ").append(schema).append('.').append(tableName);
		if (!primaryKeyColumns.isEmpty()) {
			sb.append(" WHERE ");
		}
		Iterator<Map.Entry<String, String>> iter = primaryKeyColumns.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, String> e = iter.next();
			sb.append(e.getKey()).append(" = ").append(e.getValue());
			if (iter.hasNext()) {
				sb.append(" AND ");
			}
		}
		return sb.toString();
	}
	
	public String toInsert() {
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO ").append(schema).append('.').append(tableName).append(" (");
		Iterator<String> iter = insertColumns.keySet().iterator();
		while (iter.hasNext()) {
			sb.append(iter.next());
			if (iter.hasNext()) {
				sb.append(", ");
			}
		}
		sb.append(") VALUES (");
		iter = insertColumns.values().iterator();
		while (iter.hasNext()) {
			sb.append(iter.next());
			if (iter.hasNext()) {
				sb.append(", ");
			}
		}
		sb.append(')');
		return sb.toString();
	}
	
	public String toUpdateAll() {
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE ").append(schema).append('.').append(tableName).append(" SET ");
		Iterator<Map.Entry<String, String>> iter = updateColumns.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, String> e = iter.next();
			sb.append(e.getKey()).append(" = ").append(e.getValue());
			if (iter.hasNext()) {
				sb.append(", ");
			}
		}
		return sb.toString();
	}
	
	public String toUpdateById() {
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE ").append(schema).append('.').append(tableName).append(" SET ");
		Iterator<Map.Entry<String, String>> iter = updateColumns.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, String> e = iter.next();
			sb.append(e.getKey()).append(" = ").append(e.getValue());
			if (iter.hasNext()) {
				sb.append(", ");
			}
		}
		if (!primaryKeyColumns.isEmpty()) {
			sb.append(" WHERE ");
		}
		iter = primaryKeyColumns.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, String> e = iter.next();
			sb.append(e.getKey()).append(" = ").append(e.getValue());
			if (iter.hasNext()) {
				sb.append(" AND ");
			}
		}
		return sb.toString();
	}
	
	public String toSelectAll() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		Iterator<String> iter = selectColumns.iterator();
		while (iter.hasNext()) {
			sb.append(iter.next());
			if (iter.hasNext()) {
				sb.append(", ");
			}
		}
		sb.append(" FROM ").append(schema).append('.').append(tableName).append(" AS ").append(alias).append(' ').append(join);
		return sb.toString();
	}
	
	public String toSelectById() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		Iterator<String> iter = selectColumns.iterator();
		while (iter.hasNext()) {
			sb.append(iter.next());
			if (iter.hasNext()) {
				sb.append(", ");
			}
		}
		sb.append(" FROM ").append(schema).append('.').append(tableName).append(" AS ").append(alias).append(' ').append(join);
		if (!primaryKeyColumns.isEmpty()) {
			sb.append(" WHERE ");
		}
		Iterator<Map.Entry<String, String>> iter1 = primaryKeyColumns.entrySet().iterator();
		while (iter1.hasNext()) {
			Map.Entry<String, String> e = iter1.next();
			sb.append(e.getKey()).append(" = ").append(e.getValue());
			if (iter1.hasNext()) {
				sb.append(" AND ");
			}
		}
		return sb.toString();
	}
	
	public String toSelectCountAll() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT COUNT(*) FROM ").append(schema).append('.').append(tableName).append(" AS ").append(alias).append(' ').append(join);
		return sb.toString();
	}
	
	public String toSelectCountById() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT COUNT(*) FROM ").append(schema).append('.').append(tableName).append(" AS ").append(alias).append(' ').append(join);
		if (!primaryKeyColumns.isEmpty()) {
			sb.append(" WHERE ");
		}
		Iterator<Map.Entry<String, String>> iter = primaryKeyColumns.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, String> e = iter.next();
			sb.append(e.getKey()).append(" = ").append(e.getValue());
			if (iter.hasNext()) {
				sb.append(" AND ");
			}
		}
		return sb.toString();
	}
}
